﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Web.Configuration;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace Perm.Web
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class MM
    {
        string String = "", error = null,
            selectMAX = "", selectMIN = "",
            selectMAXGAS = "", selectMINGAS = "",
            selectMAXCarwash = "", selectMINCarwash ="";

        SqlConnection conMSSQL;
        SqlDataReader readerMSSQL;
        SqlCommand commandMSSQL;

        DateTime thisDate = DateTime.Now.ToUniversalTime().AddHours(6);

        List<List<string>> list = new List<List<string>>();

        [OperationContract]
        public List<List<string>> DoWork(string city)
        {  
            string connectionStringMSSQL = WebConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString;

            try
            {
                switch (city)
                {
                    case "perm":
                        {
                            selectMAX = "SELECT TOP 3 RATING_ROAD_PERM.INFO, Avg(RATING_ROAD_PERM.RATING) AS [Avg-RATING] FROM RATING_ROAD_PERM GROUP BY RATING_ROAD_PERM.INFO HAVING (((RATING_ROAD_PERM.INFO) Is Not Null) AND ((Avg(RATING_ROAD_PERM.RATING))<>0 And (Avg(RATING_ROAD_PERM.RATING))>0 And (Avg(RATING_ROAD_PERM.RATING)) Is Not Null)) ORDER BY Avg(RATING_ROAD_PERM.RATING) DESC";
                            selectMIN = "SELECT TOP 3 RATING_ROAD_PERM.INFO, Avg(RATING_ROAD_PERM.RATING) AS [Avg-RATING] FROM RATING_ROAD_PERM GROUP BY RATING_ROAD_PERM.INFO HAVING (((RATING_ROAD_PERM.INFO) Is Not Null) AND ((Avg(RATING_ROAD_PERM.RATING))<>0 And (Avg(RATING_ROAD_PERM.RATING))<0 And (Avg(RATING_ROAD_PERM.RATING)) Is Not Null)) ORDER BY Avg(RATING_ROAD_PERM.RATING)";
                            selectMAXGAS = "SELECT TOP 3 RATING_GAS_PERM.INFO, Avg(RATING_GAS_PERM.RATING) AS [Avg-RATING] FROM RATING_GAS_PERM GROUP BY RATING_GAS_PERM.INFO HAVING (((RATING_GAS_PERM.INFO) Is Not Null) AND ((Avg(RATING_GAS_PERM.RATING))<>0 And (Avg(RATING_GAS_PERM.RATING))>0 And (Avg(RATING_GAS_PERM.RATING)) Is Not Null)) ORDER BY Avg(RATING_GAS_PERM.RATING) DESC";
                            selectMINGAS = "SELECT TOP 3 RATING_GAS_PERM.INFO, Avg(RATING_GAS_PERM.RATING) AS [Avg-RATING] FROM RATING_GAS_PERM GROUP BY RATING_GAS_PERM.INFO HAVING (((RATING_GAS_PERM.INFO) Is Not Null) AND ((Avg(RATING_GAS_PERM.RATING))<>0 And (Avg(RATING_GAS_PERM.RATING))<0 And (Avg(RATING_GAS_PERM.RATING)) Is Not Null)) ORDER BY Avg(RATING_GAS_PERM.RATING)";
                            selectMAXCarwash = "SELECT TOP 3 RATING_CARWASH_PERM.INFO, Avg(RATING_CARWASH_PERM.RATING) AS [Avg-RATING] FROM RATING_CARWASH_PERM GROUP BY RATING_CARWASH_PERM.INFO HAVING (((RATING_CARWASH_PERM.INFO) Is Not Null) AND ((Avg(RATING_CARWASH_PERM.RATING))<>0 And (Avg(RATING_CARWASH_PERM.RATING))>0 And (Avg(RATING_CARWASH_PERM.RATING)) Is Not Null)) ORDER BY Avg(RATING_CARWASH_PERM.RATING) DESC";
                            selectMINCarwash = "SELECT TOP 3 RATING_CARWASH_PERM.INFO, Avg(RATING_CARWASH_PERM.RATING) AS [Avg-RATING] FROM RATING_CARWASH_PERM GROUP BY RATING_CARWASH_PERM.INFO HAVING (((RATING_CARWASH_PERM.INFO) Is Not Null) AND ((Avg(RATING_CARWASH_PERM.RATING))<>0 And (Avg(RATING_CARWASH_PERM.RATING))<0 And (Avg(RATING_CARWASH_PERM.RATING)) Is Not Null)) ORDER BY Avg(RATING_CARWASH_PERM.RATING)";
                            break;
                        }
                    case "ekb":
                        {
                            selectMAX = "SELECT TOP 3 RATING_ROAD_EKB.INFO, Avg(RATING_ROAD_EKB.RATING) AS [Avg-RATING] FROM RATING_ROAD_EKB GROUP BY RATING_ROAD_EKB.INFO HAVING (((RATING_ROAD_EKB.INFO) Is Not Null) AND ((Avg(RATING_ROAD_EKB.RATING))<>0 And (Avg(RATING_ROAD_EKB.RATING))>0 And (Avg(RATING_ROAD_EKB.RATING)) Is Not Null)) ORDER BY Avg(RATING_ROAD_EKB.RATING) DESC";
                            selectMIN = "SELECT TOP 3 RATING_ROAD_EKB.INFO, Avg(RATING_ROAD_EKB.RATING) AS [Avg-RATING] FROM RATING_ROAD_EKB GROUP BY RATING_ROAD_EKB.INFO HAVING (((RATING_ROAD_EKB.INFO) Is Not Null) AND ((Avg(RATING_ROAD_EKB.RATING))<>0 And (Avg(RATING_ROAD_EKB.RATING))<0 And (Avg(RATING_ROAD_EKB.RATING)) Is Not Null)) ORDER BY Avg(RATING_ROAD_EKB.RATING)";
                            selectMAXGAS = "SELECT TOP 3 RATING_GAS_EKB.INFO, Avg(RATING_GAS_EKB.RATING) AS [Avg-RATING] FROM RATING_GAS_EKB GROUP BY RATING_GAS_EKB.INFO HAVING (((RATING_GAS_EKB.INFO) Is Not Null) AND ((Avg(RATING_GAS_EKB.RATING))<>0 And (Avg(RATING_GAS_EKB.RATING))>0 And (Avg(RATING_GAS_EKB.RATING)) Is Not Null)) ORDER BY Avg(RATING_GAS_EKB.RATING) DESC";
                            selectMINGAS = "SELECT TOP 3 RATING_GAS_EKB.INFO, Avg(RATING_GAS_EKB.RATING) AS [Avg-RATING] FROM RATING_GAS_EKB GROUP BY RATING_GAS_EKB.INFO HAVING (((RATING_GAS_EKB.INFO) Is Not Null) AND ((Avg(RATING_GAS_EKB.RATING))<>0 And (Avg(RATING_GAS_EKB.RATING))<0 And (Avg(RATING_GAS_EKB.RATING)) Is Not Null)) ORDER BY Avg(RATING_GAS_EKB.RATING)";
                            selectMAXCarwash = "SELECT TOP 3 RATING_CARWASH_EKB.INFO, Avg(RATING_CARWASH_EKB.RATING) AS [Avg-RATING] FROM RATING_CARWASH_EKB GROUP BY RATING_CARWASH_EKB.INFO HAVING (((RATING_CARWASH_EKB.INFO) Is Not Null) AND ((Avg(RATING_CARWASH_EKB.RATING))<>0 And (Avg(RATING_CARWASH_EKB.RATING))>0 And (Avg(RATING_CARWASH_EKB.RATING)) Is Not Null)) ORDER BY Avg(RATING_CARWASH_EKB.RATING) DESC";
                            selectMINCarwash = "SELECT TOP 3 RATING_CARWASH_EKB.INFO, Avg(RATING_CARWASH_EKB.RATING) AS [Avg-RATING] FROM RATING_CARWASH_EKB GROUP BY RATING_CARWASH_EKB.INFO HAVING (((RATING_CARWASH_EKB.INFO) Is Not Null) AND ((Avg(RATING_CARWASH_EKB.RATING))<>0 And (Avg(RATING_CARWASH_EKB.RATING))<0 And (Avg(RATING_CARWASH_EKB.RATING)) Is Not Null)) ORDER BY Avg(RATING_CARWASH_EKB.RATING)";
                            break;
                        }
                    case "permkray":
                        {
                            selectMAX = "SELECT TOP 3 RATING_ROAD_PERMKRAY.INFO, Avg(RATING_ROAD_PERMKRAY.RATING) AS [Avg-RATING] FROM RATING_ROAD_PERMKRAY GROUP BY RATING_ROAD_PERMKRAY.INFO HAVING (((RATING_ROAD_PERMKRAY.INFO) Is Not Null) AND ((Avg(RATING_ROAD_PERMKRAY.RATING))<>0 And (Avg(RATING_ROAD_PERMKRAY.RATING))>0 And (Avg(RATING_ROAD_PERMKRAY.RATING)) Is Not Null)) ORDER BY Avg(RATING_ROAD_PERMKRAY.RATING) DESC";
                            selectMIN = "SELECT TOP 3 RATING_ROAD_PERMKRAY.INFO, Avg(RATING_ROAD_PERMKRAY.RATING) AS [Avg-RATING] FROM RATING_ROAD_PERMKRAY GROUP BY RATING_ROAD_PERMKRAY.INFO HAVING (((RATING_ROAD_PERMKRAY.INFO) Is Not Null) AND ((Avg(RATING_ROAD_PERMKRAY.RATING))<>0 And (Avg(RATING_ROAD_PERMKRAY.RATING))<0 And (Avg(RATING_ROAD_PERMKRAY.RATING)) Is Not Null)) ORDER BY Avg(RATING_ROAD_PERMKRAY.RATING)";
                            selectMAXGAS = "SELECT TOP 3 RATING_GAS_PERMKRAY.INFO, Avg(RATING_GAS_PERMKRAY.RATING) AS [Avg-RATING] FROM RATING_GAS_PERMKRAY GROUP BY RATING_GAS_PERMKRAY.INFO HAVING (((RATING_GAS_PERMKRAY.INFO) Is Not Null) AND ((Avg(RATING_GAS_PERMKRAY.RATING))<>0 And (Avg(RATING_GAS_PERMKRAY.RATING))>0 And (Avg(RATING_GAS_PERMKRAY.RATING)) Is Not Null)) ORDER BY Avg(RATING_GAS_PERMKRAY.RATING) DESC";
                            selectMINGAS = "SELECT TOP 3 RATING_GAS_PERMKRAY.INFO, Avg(RATING_GAS_PERMKRAY.RATING) AS [Avg-RATING] FROM RATING_GAS_PERMKRAY GROUP BY RATING_GAS_PERMKRAY.INFO HAVING (((RATING_GAS_PERMKRAY.INFO) Is Not Null) AND ((Avg(RATING_GAS_PERMKRAY.RATING))<>0 And (Avg(RATING_GAS_PERMKRAY.RATING))<0 And (Avg(RATING_GAS_PERMKRAY.RATING)) Is Not Null)) ORDER BY Avg(RATING_GAS_PERMKRAY.RATING)";
                            selectMAXCarwash = "SELECT TOP 3 RATING_CARWASH_PERMKRAY.INFO, Avg(RATING_CARWASH_PERMKRAY.RATING) AS [Avg-RATING] FROM RATING_CARWASH_PERMKRAY GROUP BY RATING_CARWASH_PERMKRAY.INFO HAVING (((RATING_CARWASH_PERMKRAY.INFO) Is Not Null) AND ((Avg(RATING_CARWASH_PERMKRAY.RATING))<>0 And (Avg(RATING_CARWASH_PERMKRAY.RATING))>0 And (Avg(RATING_CARWASH_PERMKRAY.RATING)) Is Not Null)) ORDER BY Avg(RATING_CARWASH_PERMKRAY.RATING) DESC";
                            selectMINCarwash = "SELECT TOP 3 RATING_CARWASH_PERMKRAY.INFO, Avg(RATING_CARWASH_PERMKRAY.RATING) AS [Avg-RATING] FROM RATING_CARWASH_PERMKRAY GROUP BY RATING_CARWASH_PERMKRAY.INFO HAVING (((RATING_CARWASH_PERMKRAY.INFO) Is Not Null) AND ((Avg(RATING_CARWASH_PERMKRAY.RATING))<>0 And (Avg(RATING_CARWASH_PERMKRAY.RATING))<0 And (Avg(RATING_CARWASH_PERMKRAY.RATING)) Is Not Null)) ORDER BY Avg(RATING_CARWASH_PERMKRAY.RATING)";
                            break;
                        }
                } 

                try {
                    conMSSQL = new SqlConnection(connectionStringMSSQL);
                    conMSSQL.Open();
                } catch { error += " Невозможно подключиться к базе данных. MS SQL"; }

                top(selectMAX);
                top(selectMIN);
                top(selectMAXGAS);
                top(selectMINGAS);
                top(selectMAXCarwash);
                top(selectMINCarwash);

                if (error != null)
                {
                    switch (city)
                    {
                        case "perm":
                            {
                                String = "INSERT INTO ERRORS (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                                break;
                            }
                        case "ekb":
                            {
                                String = "INSERT INTO ERRORS_EKB (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                                break;
                            }
                        case "permkray":
                            {
                                String = "INSERT INTO ERRORS_PERMKRAY (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                                break;
                            }
                    }
                    try
                    {
                        commandMSSQL = new SqlCommand(String, conMSSQL);
                        commandMSSQL.ExecuteNonQuery();
                    }
                    catch { }
                }

                conMSSQL.Close();
            } catch { };
            return list;
        }

        List<string> top(string select)
        {
            List<string> LIST = new List<string>();
            try {
                commandMSSQL = new SqlCommand(select, conMSSQL);
                readerMSSQL = commandMSSQL.ExecuteReader();
            } catch { error += " Невозможно получить Top 3. MS SQL"; }

            while (readerMSSQL.Read())
            {
                LIST.Add(readerMSSQL.GetString(0));
            }
            if (LIST.Count < 1) LIST.Add("Не определенно");
            if (LIST.Count > 3)
            {          
                int i = 3;
                while (LIST.Count > 3)
                {
                    LIST.RemoveAt(i);
                    i++;
                }
            }
            list.Add(LIST);
            return LIST;
        }
        // Add more operations here and mark them with [OperationContract]
    }
}
